package com.eureka.bclass.util;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.NumberToTextConverter;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

/**
 * @Description: TODO Excel工具类
 * @Author: lixing lixing_java@163.com
 * @Date: 2019/10/10 21:43
 */
public class ExcelUtil {
    /**
     * @Description 解析Excel2003
     * @Author lixing
     * @Date 2019/2/15 23:42
     * @Param [inputStream 上传的Excel文件流]
     * @return
     */
    public static List<Map<Integer, String>> readXLX(InputStream inputStream) throws IOException {
        List<Map<Integer, String>> rowData = new ArrayList<>(); // 初始化rowData
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook(inputStream); // 创建文档流对象
        HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(0); // 创建Sheet工作簿
        HSSFRow hssfRow; // 创建行对象
        HSSFCell hssfCell; // 创建列对象

        /*
         * 读取第一行中的所有标题列，确定有效列的最大列数
         */
        hssfRow = hssfSheet.getRow(0);
        int lastCellNum = hssfRow.getLastCellNum();
        int maxCellNum = 0;
        for (int cellIndex = 0; cellIndex <= lastCellNum; cellIndex++) {
            hssfCell = hssfRow.getCell(cellIndex);
            if (StrUtil.isEffective(getCellValue(hssfCell))) {
                maxCellNum += 1;
            }
        }

        /*
         * 遍历当前Sheet中所有的Row，从第二行开始
         */
        Map<Integer, String> cellData;
        for (int rowIndex = 1; rowIndex <= hssfSheet.getLastRowNum(); rowIndex++) {
            hssfRow = hssfSheet.getRow(rowIndex); // 创建Row
            if (hssfRow == null) {
                continue;
            }
            boolean isEffectiveRow = true; // 默认当前行有效
            cellData = new LinkedHashMap<>(); // 初始化cellData
            /*
             * 遍历当前Row中所有的Cell
             */
            for (int cellIndex = 0; cellIndex < maxCellNum; cellIndex++) {
                hssfCell = hssfRow.getCell(cellIndex); // 创建Cell
                String cellValue = getCellValue(hssfCell); // 获取列值
                /*
                 * 当每行的第一列值无效时，则代表当前行无效
                 */
                if (cellIndex == 0 && !StrUtil.isEffective(cellValue)) {
                    isEffectiveRow = false;
                    break;
                }
                cellData.put(cellIndex, cellValue);
            }
            if (isEffectiveRow) {
                rowData.add(cellData);
            }
        }
        return rowData;
    }

    /**
     * @Description 解析Excel2007+
     * @Author lixing
     * @Date 2019/2/15 23:42
     * @Param [inputStream 上传的Excel文件]
     * @return
     */
    public static List<Map<Integer, String>> readXLXS(InputStream inputStream) throws IOException {
        List<Map<Integer, String>> rowData = new ArrayList<>(); // 初始化rowData
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook(inputStream); // 创建文档流对象
        XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0); // 创建Sheet簿
        XSSFRow xssfRow; // 创建行对象
        XSSFCell xssfCell; // 创建列对象

        /*
         * 读取第一行中的所有标题列，确定有效列的最大列数
         */
        xssfRow = xssfSheet.getRow(0);
        int lastCellNum = xssfRow.getLastCellNum();
        int maxCellNum = 0;
        for (int cellIndex = 0; cellIndex <= lastCellNum; cellIndex++) {
            xssfCell = xssfRow.getCell(cellIndex);
            if (StrUtil.isEffective(getCellValue(xssfCell))) {
                maxCellNum += 1;
            }
        }
        /*
         * 遍历当前Sheet中所有的Row，从第二行开始
         */
        Map<Integer, String> cellData;
        for (int rowIndex = 1; rowIndex <= xssfSheet.getLastRowNum(); rowIndex++) {
            xssfRow = xssfSheet.getRow(rowIndex); // 创建Row
            if (xssfRow == null) {
                continue;
            }
            boolean isEffectiveRow = true; // 默认当前行有效
            cellData = new LinkedHashMap<>(); // 初始化cellData
            /*
             * 遍历当前Row中所有的Cell
             */
            for (int cellIndex = 0; cellIndex < maxCellNum; cellIndex++) {
                xssfCell = xssfRow.getCell(cellIndex); // 创建Cell
                String cellValue = getCellValue(xssfCell); // 获取列值
                /*
                 * 当每行的第一列值无效时，则代表当前行无效
                 */
                if (cellIndex == 0 && !StrUtil.isEffective(cellValue)) {
                    isEffectiveRow = false;
                    break;
                }
                cellData.put(cellIndex, cellValue);
            }
            if (isEffectiveRow) {
                rowData.add(cellData);
            }
        }
        return rowData;
    }

    // 创建Excel2003================================================================================================

    // DataFormat
    // 文本格式
    private static short getDataFormat_text(DataFormat dataFormat) {
        return dataFormat.getFormat("@");
    }

    // 日期格式
    private static short getDataFormat_datatime(DataFormat dataFormat) {
        return dataFormat.getFormat("yyyy-MM-dd hh:mm:ss");
    }

    // Font
    // 表头
    private static Font getFont_header(Workbook workbook) {
        Font font = workbook.createFont();
        font.setBold(true);
        font.setFontHeightInPoints((short) 26);
        font.setColor(IndexedColors.WHITE.index);
        return font;
    }

    // 标题
    private static Font getFont_title(Workbook workbook) {
        Font font = workbook.createFont();
        font.setBold(true);
        font.setColor(IndexedColors.RED.index);
        font.setFontHeightInPoints((short) 10);
        return font;
    }

    // 内容
    private static Font getFont_context(Workbook workbook) {
        return workbook.createFont();
    }

    // CellStyle
    // 表头
    public static CellStyle getCellStyle_header(Workbook workbook) {
        CellStyle cellStyle_header = workbook.createCellStyle();
        cellStyle_header.setVerticalAlignment(VerticalAlignment.CENTER); // 垂直居中
        cellStyle_header.setAlignment(HorizontalAlignment.CENTER_SELECTION); // 水平居中
        cellStyle_header.setFillForegroundColor(IndexedColors.AQUA.getIndex()); // 背景颜色
        cellStyle_header.setFillPattern(FillPatternType.SOLID_FOREGROUND); // 前景色
        cellStyle_header.setFont(getFont_header(workbook));
        return cellStyle_header;
    }

    // 标题
    public static CellStyle getCellStyle_title(Workbook workbook) {
        CellStyle cellStyle_field = workbook.createCellStyle();
        cellStyle_field.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle_field.setAlignment(HorizontalAlignment.LEFT);
        cellStyle_field.setFont(getFont_title(workbook));
        return cellStyle_field;
    }

    // 文本格式内容
    public static CellStyle getCellStyle_body_text(Workbook workbook, DataFormat dataFormat) {
        CellStyle cellStyle_body_text = workbook.createCellStyle();
        cellStyle_body_text.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle_body_text.setAlignment(HorizontalAlignment.LEFT);
        cellStyle_body_text.setFont(getFont_context(workbook));
        cellStyle_body_text.setDataFormat(getDataFormat_text(dataFormat));
        return cellStyle_body_text;
    }

    // 日期格式内容
    public static CellStyle getCellStyle_body_datatime(Workbook workbook, DataFormat dataFormat) {
        CellStyle cellStyle_body_text = workbook.createCellStyle();
        cellStyle_body_text.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle_body_text.setAlignment(HorizontalAlignment.LEFT);
        cellStyle_body_text.setFont(getFont_context(workbook));
        cellStyle_body_text.setDataFormat(getDataFormat_datatime(dataFormat));
        return cellStyle_body_text;
    }

    /**
     * @Description 创建表头行
     * @Author lixing
     * @Date 2019/6/25 8:51
     * @Param [workbook 工作簿, sheetName sheet名称, headerName 表头名称, headerLineHeight 表头行高, fieldNames 标题列]
     * @return org.apache.poi.ss.usermodel.Sheet
     */
    public Sheet createSheet(Workbook workbook, String sheetName, String headerName, int headerLineHeight, String[] fieldNames) {
        // 创建Sheet
        Sheet sheet = workbook.createSheet(sheetName);
        Row row;
        Cell cell;

        if (headerLineHeight > 0) {
            // 合并表头
            CellRangeAddress cellAddresses = new CellRangeAddress(0, headerLineHeight - 1, 0, fieldNames.length - 1);
            sheet.addMergedRegion(cellAddresses);
            // 创建第一行
            row = sheet.createRow(0);
            row.setHeight((short) (40 * 20)); // 行高
            // 第一行中创建第一列
            cell = row.createCell(0);
            cell.setCellStyle(getCellStyle_header(workbook)); // 样式
            cell.setCellValue(headerName); // 内容
        }

        // 创建第(headerLineHeight+1)行
        row = sheet.createRow(headerLineHeight);
        row.setHeight((short) (26.25 * 20)); // 行高
        // 第(headerLineHeight+1)行行中循环创建列
        for (int i = 0; i < fieldNames.length; i++) {
            cell = row.createCell(i, CellType.STRING); // 创建列
            cell.setCellStyle(getCellStyle_title(workbook)); // 样式
            cell.setCellValue(fieldNames[i]); // 内容
            sheet.setColumnWidth(i, 20 * 256); // 列宽
        }

        return sheet;
    }

    /**
     * @Description 创建标题行（首行）
     * @Author lixing
     * @Date 2019/6/25 17:06
     * @Param [sheet 工作表, headerLineHeight 表头行高, cellStyle 列样式, titleCells 标题列数组, titleCellLen 标题列数组长度]

     */
    public static void createTitleRow(Sheet sheet, int headerLineHeight, CellStyle cellStyle, String[] titleCells, int titleCellLen) {
        Row titleRow = sheet.createRow(headerLineHeight);
        for (int cellIndex = 0; cellIndex < titleCellLen; cellIndex++) {
            Cell cell = titleRow.createCell(cellIndex);
            cell.setCellStyle(cellStyle);
            cell.setCellValue(titleCells[cellIndex]);
        }
    }

    /**
     * @Description 获取Excel指定列的值
     * @Author lixing
     * @Date 2019/2/15 23:42
     * @Param [cell]
     * @return java.lang.String
     */
    private static String getCellValue(Cell cell) {
        if (cell == null) {
            return ""; // 不能返回null，否则写入Excel会报错
        }
        String cellValue;
        CellType cellType = cell.getCellType();
        switch (cellType) {
            case STRING: // 字符串型
                cellValue = StrUtil.isEffective(cell.getStringCellValue()) ? cell.getStringCellValue().trim() : "";
                break;
            case NUMERIC: // 数值型。整数、小数、日期
                cellValue = NumberToTextConverter.toText(cell.getNumericCellValue());
                break;
            case BOOLEAN: // 布尔型
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            default:
                cellValue = "";
                break;
        }
        return cellValue;
    }

    public static void setColumnWidth(Sheet sheet, String[] titleCells) {
        int titleCellLen = titleCells.length;
        // 设置列宽
        for (int i = 0; i < titleCellLen; i++) {
            switch (titleCells[i]) {
                case "账号":
                    sheet.setColumnWidth(i, 20 * 256);
                    break;
                case "年龄":
                    sheet.setColumnWidth(i, 20 * 256);
                    break;
                case "性别":
                    sheet.setColumnWidth(i, 20 * 256);
                    break;
                case "手机号码":
                    sheet.setColumnWidth(i, 40 * 256);
                    break;
                case "注册时间":
                    sheet.setColumnWidth(i, 40 * 256);
                    break;
                default:
                    sheet.setColumnWidth(i, 10 * 256);
                    break;
            }
        }
    }

    /**
     * @Description 分页导出时数据有问题（不会创建新的sheet）\ 新增表头和标题栏
     * @Author lixing
     * @Date 2020/8/9 20:52
     */
//    public static void main(String[] args) {
//        // 查询用户信息
//        List<AdminEntity> exportData = new ArrayList<>();
//        exportData.add(new AdminEntity("账号1", 28, 1, "18013896707", new Date()));
//        exportData.add(new AdminEntity("账号2", 29, 1, "18013896708", new Date()));
//        exportData.add(new AdminEntity("账号3", 30, 1, "18013896709", new Date()));
//        exportData.add(new AdminEntity("账号4", 31, 1, "18013896700", new Date()));
//        exportData.add(new AdminEntity("账号5", 31, 1, "18013896700", new Date()));
//        exportData.add(new AdminEntity("账号6", 31, 1, "18013896700", new Date()));
//        exportData.add(new AdminEntity("账号7", 31, 1, "18013896700", new Date()));
//        exportData.add(new AdminEntity("账号8", 31, 1, "18013896700", new Date()));
//        exportData.add(new AdminEntity("账号9", 31, 1, "18013896700", new Date()));
//        exportData.add(new AdminEntity("账号10", 31, 1, "18013896700", new Date()));
//        exportData.add(new AdminEntity("账号11", 31, 1, "18013896700", new Date()));
//
//        // 导出文件的名称
//        String exportName = "管理员信息";
//        // 表头列名称
//        String headTitle = "管理员信息";
//        // 标题列名称
//        String[] titleCells = {"账号", "年龄", "性别", "手机号码", "注册时间"};
//
//        int headerHeight = 32;
//        int titleHeight = 28;
//        int bodyHeight = 24;
//        int titleCellLen = titleCells.length;
//
//        // 创建工作簿
//        SXSSFWorkbook workbook = new SXSSFWorkbook();
//        // 表头列样式
//        CellStyle cellStyle_header = com.hlkj.warboot.configurer.util.ExcelUtil.getCellStyle_header(workbook);
//        // 标题列样式
//        CellStyle cellStyle_title = com.hlkj.warboot.configurer.util.ExcelUtil.getCellStyle_title(workbook);
//        // 创建数据格式对象
//        DataFormat dataFormat = workbook.createDataFormat();
//        // 文本格式的列样式
//        CellStyle cellStyle_body_text = com.hlkj.warboot.configurer.util.ExcelUtil.getCellStyle_body_text(workbook, dataFormat);
//        // 日期格式的列样式
//        CellStyle cellStyle_body_datetime = com.hlkj.warboot.configurer.util.ExcelUtil.getCellStyle_body_datatime(workbook, dataFormat);
//
//        /**
//         * @Description TODO 创建数据行
//         */
//        // 当前待导出数据的条数
//        int exportRows = exportData.size();
//        // 默认每个Sheet工作表最多存储65535行数据
//        int sheetSize = 10;
//        // 当前工作表
//        Sheet sheet = null;
//        // 当前工作表的索引
//        int currentSheetIndex = 0;
//        //
//        Row rowHeader = null;
//        Row rowTitle = null;
//        Row rowBody = null;
//        for (int rowNumIndex = 0; rowNumIndex < exportRows; rowNumIndex++) {
//            boolean flag = (rowNumIndex + 1) > sheetSize && (rowNumIndex + 1) % sheetSize == 1;
//            if (rowNumIndex == 0 || flag) {
//                currentSheetIndex += 1;
//                System.out.println(MessageFormat.format("currentSheetIndex: {0}，rowNumIndex: {1}", currentSheetIndex, rowNumIndex));
//
//                sheet = workbook.createSheet(MessageFormat.format("第{0}页", currentSheetIndex));
//                // 设置当前sheet的列宽
//                setColumnWidth(sheet, titleCells);
//
//                // 创建表头行
//                rowHeader = sheet.createRow(0);
//                rowHeader.setHeightInPoints((short) headerHeight);
//                // 合并表头行，参数为(第一行，最后一行，第一列，最后一列)
//                sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, titleCellLen - 1));
//                //
//                Cell cell_header = rowHeader.createCell(0);
//                cell_header.setCellValue(headTitle);
//                cell_header.setCellStyle(cellStyle_header);
//
//                // 创建标题行
//                rowTitle = sheet.createRow(1);
//                rowTitle.setHeightInPoints((short) titleHeight);
//                for (int i = 0; i < titleCellLen; i++) {
//                    Cell cell = rowTitle.createCell(i);
//                    cell.setCellValue(titleCells[i]);
//                    cell.setCellStyle(cellStyle_title);
//                }
//            }
//
//            if (currentSheetIndex == 1) {
//                rowBody = sheet.createRow(rowNumIndex + 2); // 2表示表头行和标题行所占据的函数
//            } else {
//                rowBody = sheet.createRow(rowNumIndex % sheetSize == 0 ? 2 : (rowNumIndex % sheetSize + 2));
//            }
//            rowBody.setHeightInPoints((short) bodyHeight);
//
//            // 创建列
//            for (int cellNum = 0; cellNum < titleCellLen; cellNum++) {
//                Cell cell = rowBody.createCell(cellNum);
//                cell.setCellStyle(cellStyle_body_text);
//                switch (cellNum) {
//                    case 0:
//                        String account = exportData.get(rowNumIndex).getAccount();
//                        cell.setCellValue(StrUtil.isEffective(account) ? account : ""); // 账号
//                        break;
//                    case 1:
//                        Integer age = exportData.get(rowNumIndex).getAge();
//                        cell.setCellValue(age == null ? "" : age.toString()); // 年龄
//                        break;
//                    case 2:
//                        Integer sex_ = exportData.get(rowNumIndex).getSex();
//                        String sex;
//                        if (SysStateEnum.SEX_MALE.getValue() == sex_) {
//                            sex = "男";
//                        } else if (SysStateEnum.SEX_FEMALE.getValue() == sex_) {
//                            sex = "女";
//                        } else {
//                            sex = "未知";
//                        }
//                        cell.setCellValue(sex); // 性别
//                        break;
//                    case 3:
//                        String phone = exportData.get(rowNumIndex).getPhone();
//                        cell.setCellValue(StrUtil.isEffective(phone) ? phone : ""); // 手机号码
//                        break;
//                    case 4:
//                        cell.setCellStyle(cellStyle_body_datetime);// 样式
//                        Date createTime = exportData.get(rowNumIndex).getCreateTime();
//                        cell.setCellValue(createTime == null ? "" : DateUtil.dateToDateTimeCharacter(createTime)); // 注册时间
//                        break;
//                }
//            }
//        }
//
//        String classpath = null;
//        try {
//            classpath = ResourceUtils.getURL("classpath:").getPath();
//            classpath = classpath + File.separator + "static" + File.separator + "upload";
//        } catch (FileNotFoundException e) {
//            e.printStackTrace();
//        }
//        // C:\A_JAVA\workspace\osc\snoob\out\production\classes
//        // C:\A_JAVA\tomcat\tomcat-8.5.37\webapps\snoob\WEB-INF\classes
//        String absoluteClasspath = new File(classpath).getAbsolutePath();
//        // 创建目录
//        Path path = Paths.get(absoluteClasspath);
//        if (!Files.exists(path)) {
//            try {
//                Files.createDirectories(path);
//            } catch (IOException e) {
//                e.printStackTrace();
//            }
//        }
//        String fileName = absoluteClasspath + File.separator + exportName + ".xls";
//
//        // TODO 输出到本地
//        BufferedOutputStream bufferedOutputStream = null;
//        try {
//            bufferedOutputStream = new BufferedOutputStream(new FileOutputStream(new File(fileName)));
//            workbook.write(bufferedOutputStream);
//            bufferedOutputStream.flush();
//        } catch (IOException e) {
//            e.printStackTrace();
//        } finally {
//            if (null != bufferedOutputStream) {
//                try {
//                    bufferedOutputStream.close();
//                } catch (IOException e) {
//                    e.printStackTrace();
//                }
//                try {
//                    workbook.close();
//                } catch (IOException e) {
//                    e.printStackTrace();
//                }
//            }
//        }
//
//        // TODO 输出到浏览器
////        BufferedOutputStream bufferedOutputStream = null;
////        try {
////            bufferedOutputStream = new BufferedOutputStream(new FileOutputStream(new File(fileName)));
////            workbook.write(bufferedOutputStream);
////            bufferedOutputStream.flush();
////        } catch (IOException e) {
////            e.printStackTrace();
////        } finally {
////            if (bufferedOutputStream != null) {
////                try {
////                    bufferedOutputStream.close();
////                } catch (IOException e) {
////                    e.printStackTrace();
////                }
////            }
////            try {
////                workbook.close();
////            } catch (IOException e) {
////                e.printStackTrace();
////            }
////        }
//
//    }

}
